# Copyright (c) 2010, College Center for Library Automation

# See the included Copyright.txt file for full license

use strict;
use warnings;
#use PDF::ReportWriter;

require "ReportWriter150.pm";
use avsCommon;

use DBI;
use DBD::ODBC;
use Date::Calc qw(Decode_Month Today Month_to_Text);


my ($dbhstats, $sqlstats, $sthstats);
my ($vendor, $college, $source, $month, @searches, $type);
my @dbdata = ();

my $runforcollegeid = paramscheck();

my ($month_cnt, $year_start, $fyend, $fystart);

if ($begmonth <= 6) {
	$fyend = $begyear;
	$fystart = $begyear - 1;
}
else {
	$fyend = $begyear + 1;
	$fystart = $begyear;
}

print "#info: fiscal year $fystart - $fyend\n";

my @colleges;
my ($collegeid, $collegename, $aleph);

$dbhstats = DBI->connect('dbi:ODBC:AutomatedVendorStats' ,,, { 'PrintError' => 1, 'RaiseError' => 1 })
	or die '#error: dbhlogin connect: ' . DBI->errstr;
	
#get all the college information we need
my $sql = 'SELECT CollegeId, ShortName, Aleph FROM Colleges ';
if ($runforcollegeid) {
	if (length $runforcollegeid > 2) {
		$runforcollegeid =~ /(\d{1,2})-(\d{1,2})/;
		$sql .=  " WHERE CollegeID BETWEEN $1 and $2 ORDER BY CollegeId";
	}
	else {
		$sql .=  " WHERE CollegeID = $runforcollegeid";
	}
}
else {
	$sql .= ' ORDER BY CollegeId';
}

$sthstats = $dbhstats->prepare($sql);
	
$sthstats->execute();
@colleges = @{$sthstats->fetchall_arrayref()};
$sthstats->finish;

#actually prepare to get the data for report
$sqlstats = qq/EXEC GetFYStatsPivot ?,?/;
$sthstats = $dbhstats->prepare($sqlstats);

# prepare to build a csv file after pdf file
use Text::CSV;
my $csv=Text::CSV->new();

# loop through each college to send data to pdf and csv 
foreach my $college (@colleges) {
	($collegeid, $collegename, $aleph) = @{$college};
	
	print "#info: $collegename\n";
		
	# set up pdf xml
	setupXML();
	
	# get fy data for college
	$sthstats->execute($endyear.'-'.$endmonth.'-'.$endday, $collegeid);
	@dbdata = @{$sthstats->fetchall_arrayref()};

	# prepare and run the pdf
	my $rw = PDF::ReportWriter->new();
	$rw->render_report("../Temp/Rpt_L010.tmp",  \@dbdata);
	my $rptname = '../Reports/L010_M_'.$aleph.'___'. sprintf('%04d%02d%02d', $endyear, $endmonth, $endday);
	$rw->{destination}=$rptname. '.pdf';
	$rw->save();

	# open and send data to the csv file
	open CSV, ">:utf8", "$rptname.csv";
	print CSV 'College,Vendor,DataSource,SW', map (',"'.$fystart.'-'.$_.'"', (7..12)),map (',"'.$fyend.'-'.$_.'"', (1..6)),",FY Total\n";
	
	foreach my $datarow (@dbdata) {
		if ($csv->combine(@{$datarow})) {
			print CSV $csv->string(),"\n";
		}
	}

	close CSV
}

$sthstats->finish if ($sthstats);
$dbhstats->disconnect if ($dbhstats);

# current PDF software does not allow much flexibility in setting header text
# so we read the XML report definition and change the information as needed. write
# to a tmp xml file that is read by the PDF creator pm
sub setupXML {
	use XML::Twig;
	use IO::File;

	$month_cnt = 1;
	$year_start = $fystart;

	my $file = 'Rpt_L010.xml';
	my $twig= new XML::Twig(
		TwigHandlers => {
			'page/header/cell[@name="month"]'   => \&heading_handler_months,
			'page/header/cell[@name="rundate"]' => \&heading_handler_rundate,
			'page/header/cell[@name="college"]' => \&heading_handler_college},
		TwigRoots => {report => 1});
	$twig->parsefile($file);

	my $fh = new IO::File("../Temp/Rpt_L010.tmp", "w");
	$twig->print($fh);
	close $fh;

	# put the FY on the month abbvreviations
	sub heading_handler_months {
		my ($twig_obj, $month_cell) = @_;
		my $month_text = $month_cell->first_child_text('text');

		$year_start++ if $month_cnt++ == 7;

		$month_cell->first_child('text')->set_text($month_text.' '.substr("0$year_start",-2));
	}

	# put the college name in the header
	sub heading_handler_college {
		my ($twig_obj, $college_cell) = @_;
		$college_cell->first_child('text')->set_text("Vendor Statistics - $collegename Searches");
	}

	# put the run date in the header
	sub heading_handler_rundate {
		my ($twig_obj, $rundate_cell) = @_;
		my ($tyear,$tmonth,$tday) = Today;
		$rundate_cell->first_child('text')->set_text(Month_to_Text($tmonth).' '.$tday.', '.$tyear);
	}

}